﻿-- Credit where credit is due: 
-- This function retrieved from: http://stackoverflow.com/questions/10485978/creating-sub-directory-via-sql-insert-using-filetable

CREATE FUNCTION [dbo].[GetNewPathLocator]
(
	@parent hierarchyid = NULL
)
RETURNS varchar(max)
AS
BEGIN
    DECLARE @result varchar(max), @newid uniqueidentifier  -- declare new path locator, newid placeholder       
    SELECT @newid = new_id FROM dbo.GetNewID; -- retrieve new GUID      
    SELECT @result = ISNULL(@parent.ToString(), '/') + -- append parent if present, otherwise assume root
                     convert(varchar(20), convert(bigint, substring(convert(binary(16), @newid), 1, 6))) + '.' +
                     convert(varchar(20), convert(bigint, substring(convert(binary(16), @newid), 7, 6))) + '.' +
                     convert(varchar(20), convert(bigint, substring(convert(binary(16), @newid), 13, 4))) + '/'     
    RETURN @result -- return new path locator     
END
